import pandas as pd
from sqlalchemy import create_engine
from datetime import datetime
from urllib.parse import quote_plus

def calculate_age(id_number):
    """从身份证号码计算年龄"""
    if not isinstance(id_number, str) or len(id_number) not in (15, 18):
        return None
    
    # 提取出生年月日
    if len(id_number) == 18:
        birth_date_str = id_number[6:14]  # 18位身份证
    else:
        return None
    
    try:
        birth_date = datetime.strptime(birth_date_str, '%Y%m%d')
        today = datetime.now()
        age = today.year - birth_date.year
        
        # 检查是否已经过了生日
        if today.month < birth_date.month or (today.month == birth_date.month and today.day < birth_date.day):
            age -= 1
            
        return age
    except ValueError:
        return None

def export_abnormal_ages():
    """查询并导出年龄异常数据"""
    try:
        password = "123Qwe!@#"
        encoded_password = quote_plus(password)

        # 使用SQLAlchemy创建数据库连接（请根据实际情况修改）
        # engine = create_engine(
        #     'postgresql://postgres:123456@192.168.29.130:5432/manuals'
        # )

        engine = create_engine(
            f'postgresql://vsisuser:{encoded_password}@192.1.0.33:5432/vsisdb'
        )
        
        # 查询语句改为字符串形式
        query = """
            SELECT 
                axbe0031 AS 登记日期,
                axcp0002 AS 姓名,
                axcp0005 AS 身份证件号码
            FROM vsis.kdp01
            WHERE axcp0004 = '01'  -- 只查询身份证类型
              AND axcp0005 IS NOT NULL
        """
        
        # 执行查询
        df = pd.read_sql(query, engine)
        
        # 计算年龄
        df['年龄'] = df['身份证件号码'].apply(calculate_age)
        
        # 筛选异常年龄
        abnormal_df = df[(df['年龄'] > 120) | (df['年龄'] < 6)].copy()
        
        # 添加异常原因
        abnormal_df['异常原因'] = abnormal_df['年龄'].apply(
            lambda x: '年龄超过120岁' if x > 120 else '年龄小于6岁'
        )
        
        # 导出到Excel
        if not abnormal_df.empty:
            timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
            filename = r"/database/csv_file/script/年龄异常数据_{timestamp}.xlsx"
            abnormal_df.to_excel(filename, index=False)
            print(f"已导出 {len(abnormal_df)} 条异常数据到 {filename}")
        else:
            print("未发现年龄异常数据")
            
    except Exception as e:
        print(f"发生错误: {e}")

if __name__ == "__main__":
    export_abnormal_ages()